/**
 * 
 */
package com.easidea.tim.dao.report;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.easidea.tim.entity.report.StorageYearlyReportDTO;

/**
 * 统计报表专用公共Dao，不包含任何entity数据，全部使用hibernate callback来完成查询结果数据到DTO的转换
 * 
 * @author Harrison
 * 
 */
public class ReportDao extends HibernateDaoSupport {

	/**
	 * 年度库存报表
	 * 
	 * @return
	 */
	public List<StorageYearlyReportDTO> getStorageYearlyReport(Integer year) {
		final String sql = "SELECT CONCAT(DATE_FORMAT(create_time,'%m'),'月') AS MONTH_STR, "
				+ "SUM(CASE sample_flag WHEN 1 THEN meter_num END) AS SAMPLEQUANTITY, "
				+ "SUM(CASE sample_flag WHEN 0 THEN meter_num END) AS PRODUCTQUANTITY "
				+ "FROM tim_inventory_storage WHERE  deleted = 0 AND  "
				+ "YEAR(create_time) = "
				+ year
				+ " GROUP BY DATE_FORMAT(create_time,'%m');";

		return (List<StorageYearlyReportDTO>) getHibernateTemplate()
				.executeFind(new HibernateCallback() {

					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						List<StorageYearlyReportDTO> list = null;

						SQLQuery query = session.createSQLQuery(sql);
						query.addScalar("MONTH_STR");
						query.addScalar("SAMPLEQUANTITY");
						query.addScalar("PRODUCTQUANTITY");

						List<Object[]> rst = query.list();
						list = new ArrayList<StorageYearlyReportDTO>();

						for (Object[] obj : rst) {
							StorageYearlyReportDTO dto = new StorageYearlyReportDTO();
							if (obj[0] != null)
								dto.setMonth((String) obj[0]);

							if (obj[1] != null)
								dto.setSampleQuantity(((BigDecimal) obj[1]).doubleValue());
							else
								dto.setSampleQuantity(0d);

							if (obj[2] != null)
								dto.setProductQuantity(((BigDecimal) obj[2]).doubleValue());
							else
								dto.setProductQuantity(0d);

							list.add(dto);
						}
						return list;
					}
				});
	}

}
